Software Vault: The Diamond Collection

home *** CD-ROM | disk | FTP | other *** search

/ Software Vault: The Diamond Collection / The Diamond Collection (Software Vault)(Digital Impact).ISO / cdr02 / fwsb30z4.zip / 24.DOC < prev next >

Wrap

Text File | 1993-02-08 | 28KB | 735 lines

General Ledger/Lotus 1-2-3 Import/Export ========================== Lotus 1-2-3 Tools ----------------- FW can import and export to Lotus 1-2-3 WK1 files. These files are compatible with Excel, Quattro, SuperCalc 5, and other programs that can read and write WK1 files. These files may also be used as WKS files. Most accounting programs can import/export financial information through Lotus 1-2-3 files, or a file format the Lotus 1-2-3 can import/export to. This allows FW to operate with most accounting systems. FW can be used to import the following: * Current year amounts * Budgeted amounts * Previous year amounts Our "setup" file may be used to summarize accounts, as multiple accounts may be combined into one FW account. This allows FW to be used to create complete sets of financial reports, either detailed or summarized. FW can export: * Data Entry amounts * Monthly or summarized amounts * Formal sets of Financial Statements * FASB 95 Cash Reports The formal sets of financial statements and the FASB 95 cash reports were covered in an earlier chapter. These reports are available under the menu selection "Lotus Conv". Just as our "setup" file may be used to summarize accounts, the same file may be used to export accounts in detailed fashion. A percentage of one FW account may be exported to multiple accounts (rows within Lotus 1-2-3). In this way budgets can be prepared from summarized prior-year amounts, forecast as a smaller set of accounts, and exported as multiple accounts. For example, if a company has 20 telephone expenses for 20 departments, all 20 accounts may be loaded into one FW account, forecast for the next year within FW, and then exported to Lotus 1-2-3 as 20 accounts. The Power of Combining Your Accounting System & FW ================================================== Budgeting --------- FW uses sophisticated spreading techniques that enable users to forecast on an annual or other basis, creating complete sets of monthly financial statements. In this way, seasonality and links to other accounts such as linking cost of sales to sales can be accomplished. FW can then rewrite the monthly budget amounts back into a Lotus 1-2-3 file format for import into your accounting system. Financial Analysis ------------------ FW will help accounting system customers evaluate expected financial results. "What-if" analysis becomes an intrinsic part of the accounting process. Ratio analysis can be used to "grade" the expected results against company expectations or against the performance of competitors. Financial Reporting ------------------- FW produces complete sets of monthly financial statements -- not just general ledger accounts. All of the amounts in your general ledger ("GL") accounts can be loaded into FW (through 1-2-3 files), or FW can be used to summarize accounts into financial statements. FASB 95 cash statements may be produced using either the direct or indirect methods. FW can play an important part in your monthly, quarterly, and annual financial reporting. Lotus 1-2-3 Conversion ---------------------- FW can export its finished set of financial statements to WK1 files, with just ONE KEYSTROKE! This is covered under the menu selection "Lotus Conv". An additional 1-2-3 export has been provided. FW can create a WK1 file with your GL's account number, account description, and selected amounts. This allows users to import GL accounts, and export directly into WK1 file format. This option is covered under the menu selection "Load/Imp/Exp", which will be explained in this chapter. Balance Sheet Modifications --------------------------- FW displays all balance sheet amounts as ending balances as of the end of each month, quarter or year. Many GL accounting systems display the opening balance and for each month the total transactions for that month. FW can import/export GL amounts as either balances as of a particular date, or as increases or decreases to existing balances. GL systems store contra accounts, such as allowance for bad debts and accumulated depreciation two ways. As positive amounts with a notation that the account should be subtracted, or as negative amounts. FW handles these amounts as positive amounts, however since the sign (+ or -) of any account can be reversed when imported, FW can handle either occurrence. FW only deals with whole dollars. When importing amounts, FW will round-off amounts. Exporting a percentage of an account will result in a round-off to the nearest penny. Financial Accounts ------------------ The great strength of FW is its structure. While most GL packages are flexible, financial reports, budgets, cash reports and financial analysis must be built by the user in their accounting system report writer or third party package such as Lotus 1-2-3. FW includes a pre-defined set of financial accounts. The accounting logic is already included for each account. This makes reporting, budgets and financial analysis simple to obtain. While pre-defined, FW allows the user to create thousands of sub- accounts to mirror their general ledger. However, for report writing, you may wish to use FW to summarize your GL into a more compressed set of financials. the choice is yours. GL Account Setup ================ Before we can import GL amounts through Lotus 1-2-3, we need to setup an import file. Run the FW program and select a "new" or a "previously saved file." NOTE: Since FW imports amounts into the currently active file, you must load a file at the start of the program before FW will display its import/export options. After a file has been loaded, move the top menu listing to "Files", and press enter. Then select the top choice "Load/Imp/Exp" from the pull-down listing. The following screen will be displayed. We are now ready to proceed. Pull-down the "Files" menu selection, and select "Create/Modify Import/Export Setup." At this time you should verify that the FW file presently in memory is the file to be used for the GL import. Add additional accounts into the FW spreadsheet as needed, to arrive at the final structure of your FW BUDGET, RPT, or CONSOLIDATION file. In the future, you will be able to create templates of existing .IMP files, so that this process will only need to be updated for new accounts added to your GL. Also, different import setup files can be maintained for different purposes. FW cannot use the account numbers used within your GL file. A setup file of your GL accounts and your FW accounts needs to be set up. The files created under this option will be stored as (.IMP) files. Note: When importing your GL accounts, you have several options. * Set up a corresponding account in FW for each GL account. * Combine some or all accounts into a smaller number of FW accounts. * Import some but not all GL accounts. You may use the same setup file for import and export. You may also modify and save an .IMP import file under a different name. When combining multiple accounts, FW allows you to later export a percentage of the combined FW results to various OSAS accounts. In this way budgeting telephone expense, for example, for 10 departments can be budgeted as one amount within FW, with a percentage exported through a WK1 file to each of the 10 accounts within your GL. Let's load a previously created .IMP file. We will select the option "Load/Modify Import Setup." From the list of .IMP files, we see the file we wish to load, "LOT1.IMP". This is a previously created file. Move the cell- block over the file name, and press the function key "F10." It is assumed that you have already added any new sub-accounts to the existing BUDGET (or other) file. As a safety feature, FW will not allow you to enter an FW account that has not been created. A quick review of this file shows that the first GL account is "50". We decided that this GL cash account will be combined into the FW cash account 100000. If we have created additions sub-accounts in the current FW file, account #50 can be been placed into a sub-account. When this file was created, we entered 100000 as the FW account number. FW entered the account description "Cash" automatically. This is the account description within the FW spreadsheet. Instead of just a sea of financial account numbers, you can be assured that you have entered the correct account number. If FW finds account number "50" in the Lotus import file, those amounts will be imported into account 100000 within the FW spreadsheet. The second matchup includes a couple of new items. First, you will notice the import number "5a". Some accounting systems allow account numbers to be any alphanumeric character. FW is flexible enough to import numbers, letters, and other characters including spaces to the left and between the other characters. The second item of note is that we have selected to import the account description on the second matchup. The Lotus file's account description will be imported into FW's account number 103000. Entries with no matchup, of an FW and an Import number, will be ignored when the file is imported. The use of F1 and CTRL-F1 allow you to move forward and backward through the 10 pages of 500 rows each. Similar to our regular spreadsheet, the "INS" key will allow new rows to be inserted, and "CTRL + F8" will delete a row. Each .IMP file holds 5000 rows. Multiple .IMP files may be used to load accounts into FW's spreadsheet, 5000 rows at a time. Inserting a new row affects the current (500 row) page only, unless all remaining rows in that page, from the point of the insertion, are in use. In this way separate pages can be used for different purposes. Deleting a row affects the current page only. Copy down commands will also speed completion. The "F2" key will copy a number down to the next row, and move the cell-block at the same time. "F3" operates 15 lines at a time, "F4" up to 500 at a time. For simplicity, 500 rows will be displayed at a time. There are 10 pages of 500 rows each, or 5000 in total. These pages are stored together within one .IMP file. Alt+P will print a worksheet similar to that displayed on the screen for all 5000 rows. However, blank cells (those with no FW or import account number) will not be printed. Alt+G will print the current page only. Lotus 1-2-3 Import ================== The SETUP screen has several options. FW A/C # & Account Description ------------------------------ Enter the FW account number. FW will fill in the account description for you. The current FW file must contain all of the accounts and subaccounts to be used by the SETUP file. Multiple Import Accounts can be combined into one FW account. This is useful for combining accounts for financial reporting or budgeting. Import A/C # ------------ Enter the Lotus file account number. This will typically be the account number from your GL program, exported to Lotus file format. FW will import your account number as either a label or a number. Rv$ --- "Rv$" reverses the amount, positive to negative or vise-versa. This may be needed with contra accounts such as "allowance for bad debts" and "accumulated depreciation." FW stores these contra accounts as positive amounts. If your Lotus file contains these accounts as negative amounts, add (Y)es to import as positive amounts (this assumes that you are importing these amounts into FW accounts recognized as contra type accounts such as "allowance for bad debts" and "accumulated depreciation"). Answer (Y)es to import a reverse of the amount. ImNm ---- "ImNm" imports the account description from the Lotus file. Answer (Y)es to import the account description. The description will be limited to the first 21 characters. %Imp ---- "%Imp" imports a percentage of the amounts for that account. This is useful for minority ownership and currency conversion. Enter 100.000% if the full amount should be imported. %Exp ---- "%Exp" exports a percentage to a Lotus file. This same setup file can be used for import and export, so that your GL or other program can import or export a similar Lotus file layout. This is useful for minority ownership and currency conversion. Useful in Preparing Budgets --------------------------- One FW account can be exported to multiple export account numbers. This allows the budgeting of one amount to be spread to multiple departments for export into other accounting systems. Enter 100.000% if the full amount should be imported. Let's say we add a third row, but only enter the import account number (leave the FW account number open. At this point we might decide to leave this file. Press '/' to leave. In this example, there are now 3 Import entries, but only 2 FW entries. Because you might have made an error, and missed entering an account number, FW prompts you with an error message. You can ignore this message by pressing 'Y' to continue. Changes made to the .IMP file will NOT be automatically saved. A choice will be presented to either save or not save your file. This choice will appear weather or not you have modified the current .IMP file. FW will now prompt you for the name of the .IMP file just created. In this example we have chosen, and typed-in, the name "LOT1". You may select any name you wish. FW will save our work into a file named "LOT1.IMP", FW having added the extension of .IMP. Note: If you were to press the "ESC" key at this time, the file and our work, would not be saved. After typing the LOT1, and pressing the "ENTER" key, FW beeped us, notifying us that a file using the name LOT1.IMP was previously saved. After deciding that the previously saved file is no longer needed, we press 'Y' for yes. A message appears noting that the file has been saved. Lotus 1-2-3 Import ================== Now that we have a setup file, let's import the Lotus file accounts into FW. Move the top menu listing to "Files", and press enter. Then select the top choice "Load/Imp/Exp" from the pull-down listing. Select the option "Lotus Import into BUDGET/RPT/CONSOL file." The Lotus import screen has several options. Name of 1-2-3 file ------------------ Enter the exact location and name of your 1-2-3 file. This should include the drive letter, subdirectory (if needed), and the file name. Under MS-DOS, you may enter names in upper or lower case. Lotus row number ---------------- Starting row number ------------------- Enter the starting row for import into FW. This allows you to import WK1 files that may include headers. The starting row number should be between 1 and 8192. Ending row number ----------------- Enter the ending row for import into FW. The ending row number should be between 1 and 8192. If you are importing just one row, then both the starting and the ending row numbers with be the same. Lotus column number ------------------- Financial Account # ------------------- Enter the column containing your GL account number. The starting column number should be between 1 and 256. FW asks that you enter the column by number, not by letter as is used within Lotus 1-2-3. However, FW will display the equivalent Lotus 1-2-3 column letter. Using numbers should be simpler than using the alphabet, when setting a range of 12 or 36 columns. Account Description ------------------- Enter the column containing your GL account description. The starting column number should be between 1 and 256. If no description is included in your file, please create a blank column in your 1-2-3 worksheet at the appropriate location. Starting column number - Amounts -------------------------------- Enter the starting column for import into FW. The starting column number should be between 1 and 256. Ending column number - Amounts ------------------------------ Enter the ending column for import into FW. The ending column number should be between 1 and 256. If you are importing just one column, then both the starting and the ending column numbers with be the same. Financial Wonder ---------------- Starting column # ----------------- Enter the starting FW column for import from 1-2-3. The starting column number should be between 0 and 36. Although FW does not label its column, its column numbers are '0' for the opening balance sheet, and "1 to 36" for the 3 year monthly periods. Its Data Entry columns are also numbered the same, although the last column may be numbered as little as '3' if 3 annual Data Entry periods are used. FW will not import I/S or C/S amounts into the opening period, as only a B/S has an opening balance. If you are importing into column '0', enter 0 into the starting column within your WK1 file to be imported, for any I/S and C/S accounts. FW will display the appropriate month/year period for your selection. This period will be modified if you change your FW import to "Data Entry" or "Monthly Calc", as noted in the option below. Overwrite amounts already in FW ? --------------------------------- FW imports amounts into an existing FW spreadsheet. If this spreadsheet already has amounts entered, you have two choices. * Add the imported amounts to the amounts already in the current file, or * Overwrite the amounts already existing. FW will overwrite only those cells of those accounts to be imported. If the current file is empty, balance sheet amounts imported will be extended through the last column, such that the last column imported will be extended through the last column. If the current file is not empty, the last B/S amount imported will be added to subsequent balances in the current file. Clearing (to $0) occurs prior to any import of amounts. Imported amounted are then "added to" or "subtracted from" the current balance or amount. In this way multiple accounts can be imported into the same FW account. Clear imported accounts through the last column ? ------------------------------------------------- An extension of the previous option, the above option must first be set to YES. This will clear the FW spreadsheet prior to any import of amounts. Only the accounts and columns to be imported will be cleared, plus any columns beyond the last columns to be imported. All other accounts and columns within the current FW spreadsheet will be unaffected. You have the option of clearing any combination of B/S, I/S or C/S accounts. Pressing 'Y' more than once provides different combinations. B/S represents (B)alance or (I)ncrease ? ---------------------------------------- This option affects B/S accounts only. FW stores B/S accounts as ending balances. However, many accounting systems store monthly transactions for balance sheet accounts instead of balances. FW can import files as either balances or transactions. Transaction amounts will be added to the current and succeeding periods. There may be times where you wish to import balance sheet transactions into FW. For example, if you are entering fixed asset accounts into Data Entry periods within FW, and you are using formula methods 105 to 108, which increase the previous balance by an amount, you may wish to import amounts as transactions, and not as balances. This can also be a convenient way to import journal entries stored in 1-2-3 format. When importing transactions into the first column in FW, and expecting balances in FW, the opening balance should already have been entered into FW. Only one selection may be made for each import. Make your selection by entering either 'B' or 'I'. Data Entry (D) or Monthly Calc (M) ? ------------------------------------ Your 1-2-3 file may be imported into either the Data Entry or Monthly Calc columns. Make your selection by entering either 'B' or 'I'. If (D)-Sum amounts? If (M)-Change to #99 ? ------------------------------------------- Your response to this question depends on your previous response. If you are importing monthly amounts into existing monthly/quarterly/annual Data Entry columns, FW can summarize your amounts on import. Enter 'Y' to have FW import and summarize amounts. If your are importing monthly amounts into the Monthly Calc columns, you may wish for FW to change the existing formula methods for those specific cells to formula method #99. Formula method #99 tells FW that on re-calculation, the monthly calc results should be used. This allows users to import monthly amounts into the resulting monthly calculation accounts, while at the same time forecasting other accounts or columns on a quarterly or annual basis. This can be a very powerful "What if" option. We strongly suggest that you select "Change to #99" if you are importing monthly amounts into a BUDGET file. Otherwise, those monthly amounts will be replaced upon the recalculation of the file ! The next step is to select the .IMP file, previously created. INPUT.WK1 is a small Lotus 1-2-3 import file created for this example. You will note that the GL account numbers, 50 & 5a, have been entered into the first column, followed by the account description, and five columns of amounts (this Lotus file may only be viewed from within Lotus 1-2-3, not FW). DEMO1.WDR should be the current file within FW, for this example. FW will now process this WK1 file, displaying its progress. The import should be fairly quick. You should now see the results of the import. In this case we have imported just two GL accounts, 50 and 5a, which have been imported into FW accounts 100000 and 103000. The amounts within the FW accounts were first cleared (set to $0). The opening B/S is 3/1989. The cash balances were entered into the next 5 columns. The account description "My Assets" for account 103000 has been imported from the 1-2-3 file. The balances of $27 and $37 at the end of the import period have been extended through the end of the second and third years. It would not have made sense to go from $27 & $37 back to $0, so the last recorded amount was extended through the remaining periods. This extension only applies to FW balance sheet accounts. No extensions are performed on I/S or C/S accounts. If we had selected to leave the existing amounts intact (not clear the account to $0, prior to the import), then the imported balances would be added to existing amounts, including those in the second and third year. For example, if the existing cash balance was $100 for all periods for the three years (and the account was not cleared), then the balance at the end of the first, second and third year would have been $127 & $137 ($100 + $27 or $37). Lotus 1-2-3 Export ================== Let's assume that FW's purpose was to create monthly budgets. We now wish to export FW amounts into the GL accounts. Move the top menu listing to "Files", and press enter. Then select the top choice "Load/Imp/Exp" from the pull-down listing. Select the option to export budgeted amounts to Lotus. The export screen, displayed above, has several options. Name of GL file --------------- Enter the exact location of the WK1 file to be created or overwritten. This should include the drive letter, subdirectory (if needed), and the file name. Under MS-DOS, you may enter names in upper or lower case. Financial Wonder ---------------- FW - Starting Column # ---------------------- The FW Data Entry or Monthly periods will be placed into corresponding columns within your 1-2-3 file. FW's Data Entry periods start with an opening balance sheet at column '0'. There may be as little as 3 columns representing 3 annual Data Entry periods, or as many as 36 for 36 monthly periods. FW's monthly Calc spreadsheet starts with an opening balance sheet at column '0'. The first 12 monthly periods (1 to 12) represent the first year, 13 to 24 the second year, and 25 to 36 the third year. Entering column '0' only affects entries from the B/S. I/S and C/S accounts would start at column '1', which in this case would be the second column. FW - Ending Column # -------------------- Enter the last FW column number to export. Data Entry (D), Calc (C) or Monthly Calc (M) ? ---------------------------------------------- Either the Data Entry, Calc or Monthly Calc columns may be exported to your 1-2-3 file. Make your selection by entering either 'D', 'C' or 'M'. Lotus row number ---------------- Starting row number ------------------- Enter the starting row for export. The starting row number should be between 1 and 8192. Lotus column number ------------------- Financial Account # ------------------- Enter the column to contain your GL account number. The starting column number should be between 1 and 256. FW asks that you enter the column by number, not by letter as is used within Lotus 1-2-3. However, FW will display the equivalent Lotus 1-2-3 column letter. Using numbers should be simpler than using the alphabet, when setting a range of 12 or 36 columns. Account Description ------------------- Enter the column to contain your GL account description. The starting column number should be between 1 and 256. Starting column number - Amounts -------------------------------- Enter the starting column for export. The starting column number should be between 1 and 256. B/S represents (B)alance or (I)ncrease ? ---------------------------------------- This option affects B/S accounts only. FW stores B/S accounts as ending balances. However, many accounting systems store monthly transactions for balance sheet accounts instead of balances. FW can export files as either balances or transactions. Only one selection may be made for each export. Make your selection by entering either 'B' or 'I'. After filling out the "Export to Lotus 1-2-3 file format" screen, FW will ask which .IMP file to use for export. The same file that was used for import can also be used for export (LOT1.IMP). FW will now process this file, displaying its progress. The export should be fairly quick. The above message will appear notifying you that the file has been exported into a WK1 file. The WK1 file, created by FW will look very similar to the one requested for import. We have tried to make the Lotus 1-2-3 file setup as simple as possible. At the same time our setup screens have tried to provide all the options, without making the process overly complex. Once your IMP file has been created, it can be quickly used over and over again. If you wish to export to Lotus 1-2-3 file format complete sets of financial statements, please use the menu selection "Lotus Conv".